package org.simplecrm.repository.event;

import org.simplecrm.domain.event.Event;
import org.simplecrm.domain.event.EventStatus;
import org.simplecrm.domain.event.eventtype.EventType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;

/**
 * Implementation for JDBC database.
 */
@Repository("eventDAO")
public class EventJdbcDAO implements EventDAO {

    private static final Logger LOGGER = LoggerFactory.getLogger(EventJdbcDAO.class);

    private static final String SELECT_ALL = "SELECT * FROM event";
    private static final String SELECT_FOR_CUSTOMER = "SELECT * FROM event WHERE customerId=?";
    private static final String SELECT_WHERE_ID = "SELECT * FROM event WHERE id=?";

    private static final String SELECT_EVENTTYPE_WHERE_ID = "SELECT * FROM EventType WHERE id=?";

    private DataSource ds;

    @Autowired
    public EventJdbcDAO(DataSource ds) {
        this.ds = ds;
    }

    @Override
    public List<Event> getEventsOfCustomer(int customerId) {

        List<Event> events = new LinkedList<>();

        try (Connection con = ds.getConnection();
             PreparedStatement statement = con.prepareStatement(SELECT_FOR_CUSTOMER,
                     ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_UPDATABLE)) {

            statement.setInt(1, customerId);

            try (ResultSet resultset = statement.executeQuery()) {

                while (resultset.next()) {

                    Event event = null;

                    int id = resultset.getInt(EVENT_ID_FIELD);

                    int eventTypeId = resultset.getInt(EVENT_TYPEID_FIELD);
                    EventType type = getEventType(con, eventTypeId);

                    String desc = resultset.getString(EVENT_DESCRIPTION_FIELD);
                    Date creationDate = resultset.getDate(EVENT_DATE_FIELD);
                    EventStatus status = EventStatus.valueOf(resultset.getString(EVENT_STATUS_FIELD));
                    int supportId = resultset.getInt(EVENT_SUPPORT_ID_FIELD);

                    event = new Event(id, type, desc, creationDate,
                            status, customerId, supportId);

                    events.add(event);
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.toString(), ex);
            events = null;
        }

        return events;
    }

    @Override
    public Event getEventById(int id) {

        Event event = null;

        try (Connection con = ds.getConnection();
             PreparedStatement statement = con.prepareStatement(SELECT_WHERE_ID,
                     ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_UPDATABLE)) {

            statement.setInt(1, id);

            try (ResultSet resultset = statement.executeQuery()) {

                while (resultset.next()) {

                    int customerId = resultset.getInt(EVENT_CUSTOMER_ID_FIELD);

                    int eventTypeId = resultset.getInt(EVENT_TYPEID_FIELD);
                    EventType type = getEventType(con, eventTypeId);

                    String desc = resultset.getString(EVENT_DESCRIPTION_FIELD);
                    Date creationDate = resultset.getDate(EVENT_DATE_FIELD);
                    EventStatus status = EventStatus.valueOf(resultset.getString(EVENT_STATUS_FIELD));
                    int supportId = resultset.getInt(EVENT_SUPPORT_ID_FIELD);

                    event = new Event(id, type, desc, creationDate,
                            status, customerId, supportId);
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.toString(), ex);
        }

        return event;
    }

    @Override
    public void deleteEvent(int id) {

        try (Connection con = ds.getConnection();
             PreparedStatement statement = con.prepareStatement(SELECT_WHERE_ID,
                     ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_UPDATABLE)) {

            statement.setInt(1, id);

            try (ResultSet resultset = statement.executeQuery()) {
                while (resultset.next()) {
                    resultset.deleteRow();
                }
            }

        } catch (SQLException ex) {
            LOGGER.error(ex.toString(), ex);
        }
    }

    @Override
    public void addNewEvent(Event newEvent) {

        try (Connection con = ds.getConnection();
             PreparedStatement statement = con.prepareStatement(SELECT_ALL,
                     ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_UPDATABLE);
             ResultSet resultset = statement.executeQuery()) {

            resultset.moveToInsertRow();

            resultset.updateInt(EVENT_TYPEID_FIELD, newEvent.getType().getId());
            resultset.updateString(EVENT_DESCRIPTION_FIELD, newEvent.getDescription());
            resultset.updateDate(EVENT_DATE_FIELD, new Date(newEvent.getCreationDate().getTime()));
            resultset.updateString(EVENT_STATUS_FIELD, newEvent.getStatus().name());
            resultset.updateInt(EVENT_CUSTOMER_ID_FIELD, newEvent.getCustomerId());
            resultset.updateInt(EVENT_SUPPORT_ID_FIELD, newEvent.getSupportId());

            resultset.insertRow();

        } catch (SQLException ex) {
            LOGGER.error(ex.toString(), ex);
        }
    }

    @Override
    public void updateEvent(Event updatedEvent) {

        try (Connection con = ds.getConnection();
             PreparedStatement statement = con.prepareStatement(SELECT_WHERE_ID,
                     ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_UPDATABLE)) {

            statement.setInt(1, updatedEvent.getId());

            try (ResultSet resultset = statement.executeQuery();) {

                while (resultset.next()) {

                    resultset.updateInt(EVENT_TYPEID_FIELD, updatedEvent.getType().getId());
                    resultset.updateString(EVENT_DESCRIPTION_FIELD, updatedEvent.getDescription());
                    resultset.updateDate(EVENT_DATE_FIELD, new Date(updatedEvent.getCreationDate().getTime()));
                    resultset.updateString(EVENT_STATUS_FIELD, updatedEvent.getStatus().name());
                    resultset.updateInt(EVENT_CUSTOMER_ID_FIELD, updatedEvent.getCustomerId());

                    Integer supportId = updatedEvent.getSupportId();
                    if (supportId == -1) {
                        supportId = null;
                    }
                    resultset.updateInt(EVENT_SUPPORT_ID_FIELD, supportId);

                    resultset.updateRow();

                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.toString(), ex);
        }
    }

    private static EventType getEventType(Connection con, int eventTypeId) throws SQLException {

        String eventTypeName;
        try (PreparedStatement regionStatement = con.prepareStatement(SELECT_EVENTTYPE_WHERE_ID);) {
            regionStatement.setInt(1, eventTypeId);
            try (ResultSet regionResultset = regionStatement.executeQuery()) {
                regionResultset.next();
                eventTypeName = regionResultset.getString("name");//TODO:hardcode
            }
        }

        return new EventType(eventTypeId, eventTypeName);
    }

}
